--ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()
DECLARE @T TABLE (Name VARCHAR(25), Team VARCHAR(25), AnnualSalary MONEY)

INSERT INTO @T (Name,Team,AnnualSalary) VALUES 
    ('Matt','SQL',2.50),('Dustin','SQL',-1800.00),('Sunita','SQL',3.00),('Nayana','SQL',100.00),('Will','SQL',0.01),
    ('Kelly','SQL',4.00),('Sharon','SQL',0.33),('Kimmie','CONFIG',225.99),('Chuck','CONFIG',8.00),('Amy','CONFIG',1.00),('Melanie','CONFIG',0.00),
    ('Lisa','CONFIG',5.00),('Bala','CONFIG',2.50),('Andrew','BEST BUY',0.25)
					    
					    
--SELECT  *
--FROM	   @T
--ORDER   BY Team ASC


/* *****ROW NUMBER***** */

--ROW NUMBER
--SELECT  ROW_NUMBER() OVER (ORDER BY Team ASC), * FROM	@T
--ROW NUMBER WITH PARTITION
--SELECT  ROW_NUMBER() OVER (PARTITION BY Team ORDER BY Name ASC), * FROM @T

/* *****RANK***** */

--RANK
--SELECT  RANK() OVER (ORDER BY Team), * FROM @T
--RANK WITH PARTITION
--SELECT  RANK() OVER (PARTITION BY Team ORDER BY Name), * FROM @T

/* *****DENSE RANK***** */

--DENSE RANK
--SELECT  DENSE_RANK() OVER (ORDER BY Team ASC), * FROM @T
--DENSE RANK WITH PARTITION
--SELECT  DENSE_RANK() OVER (PARTITION BY Team ORDER BY Name), * FROM @T

/* *****NTILE***** */

--NTILE EVEN
--SELECT  NTILE(2) OVER (ORDER BY Team ASC), * FROM @T
--NTILE ODD NUMBER
--SELECT  NTILE(3) OVER (ORDER BY Team ASC), * FROM @T
--NTILE WITH PARTITION
--SELECT NTILE(2) OVER (PARTITION BY Team ORDER BY Name), * FROM @T

/* *****WINDOWING AGGREGATES***** */

--Average
--SELECT *,AVG(AnnualSalary) OVER (PARTITION BY Team) AS AverageSalaryByTeam FROM @T ORDER BY Team

--Multiple Aggregates with different windows
--SELECT  *, 
--	   AVG(AnnualSalary) OVER (PARTITION BY Team) AS AverageSalaryByTeam,
--	   COUNT(Team) OVER (PARTITION BY Team) AS TeamMemberCount
--FROM	   @T
